#!/usr/bin/env python
#encoding: utf-8

import MySQLdb
import dns.query
import dns.update
import dns.tsigkeyring

# 通过shell命令获取key列表格式
# mysql -S /tmp/slave01.sock -e "show slave status\G" | awk -F: 'NR!=1{print $1}' | awk '{printf "\""$1"\",\n"}' > a.txt
keys = (
    "Slave_IO_State",
    "Master_Host",
    "Master_User",
    "Master_Port",
    "Connect_Retry",
    "Master_Log_File",
    "Read_Master_Log_Pos",
    "Relay_Log_File",
    "Relay_Log_Pos",
    "Relay_Master_Log_File",
    "Slave_IO_Running",
    "Slave_SQL_Running",
    "Replicate_Do_DB",
    "Replicate_Ignore_DB",
    "Replicate_Do_Table",
    "Replicate_Ignore_Table",
    "Replicate_Wild_Do_Table",
    "Replicate_Wild_Ignore_Table",
    "Last_Errno",
    "Last_Error",
    "Skip_Counter",
    "Exec_Master_Log_Pos",
    "Relay_Log_Space",
    "Until_Condition",
    "Until_Log_File",
    "Until_Log_Pos",
    "Master_SSL_Allowed",
    "Master_SSL_CA_File",
    "Master_SSL_CA_Path",
    "Master_SSL_Cert",
    "Master_SSL_Cipher",
    "Master_SSL_Key",
    "Seconds_Behind_Master",
    "Master_SSL_Verify_Server_Cert",
    "Last_IO_Errno",
    "Last_IO_Error",
    "Last_SQL_Errno",
    "Last_SQL_Error",
)

# 模拟一下slave节点列表, 设置注意实验时设置某些实例为不健康状态
conf = {
    'master':'127.0.0.1:3306',
    'slave':[
            '127.0.0.1:3307',
            '192.168.0.8:3307',
            '127.0.0.1:3308',
            '192.168.0.8:3308',
            '127.0.0.1:3309',
            '192.168.0.8:3309',
    ]
}

keyring = '25z/5wjwD4GsMgQluWagfkQ9TSqpoJzYbh/I/QEZo2M='

# 检查slave节点的状态是否正常
def checkSlaveStatus(host, port):
    try:
        conn = MySQLdb.connect(host=host, port=port, user='root', connect_timeout=1)
    except Exception, e:
        print e
        return False
    cur = conn.cursor()
    cur.execute('show slave status')
    data = cur.fetchall()    # 只获取到了冒号后边的value, key没有获取到, 和sql shell显示不同.

    # 将keys和data组合为字典的结构
    data = dict(zip(keys, data[0]))
    
    # IO/SQL Running 是否正常
    if data['Slave_IO_Running'] == 'No' or data['Slave_SQL_Running'] == 'No':
        return False
    elif data['Seconds_Behind_Master'] > 2:  # 主从复制时间持续超过2秒, 太慢了
        return False

    # 到这里肯定是没问题的了
    return True

# 将ip:port解析为主机+端口
def parseIP(s):
    host, port = s.split(':')
    return host, int(port)


# 动态更新dns记录
def dnsUpdate(zone, name, rdlist):
    key = dns.tsigkeyring.from_text({zone:keyring})
    up = dns.update.Update(zone, keyring=key)
    rdata_list = [dns.rdata.from_text(dns.rdataclass.IN, dns.rdatatype.A, i) for i in rdlist]
    ttl = 60
    rdata_set  = dns.rdataset.from_rdata_list(ttl, rdata_list)
    up.replace(name, rdata_set)
    q = dns.query.tcp(up, '127.0.0.1')
    #print q
    

if __name__ == '__main__':
    #host = '127.0.0.1' # 写IP好像连不上, 需要授权相应的主机
    #port = 3307
    alive = []
    for ip in conf['slave']:
        host, port = parseIP(ip)
        if checkSlaveStatus(host, port):
            alive.append(host)
    # 解释下这里为什么要设置slave的alive集群阈值
    # 如果不设置阈值, 那么存在健康的slave过少, 会导致slave的雪崩现象
    # 反而会影响服务的正常运行, 保证只有在一定数量情况下才更新dns记录.
    if float(len(alive))/len(conf['slave']) > 0.6:
        dnsUpdate('example.com', 's.db', alive)


# 注意：
# 1. dns服务一定要保证/var/named目录组用户有写的权限
# 2. iptables 和 selinux 一定要设置好, 最好设置为关闭状态.

